Work with sales and products data set

# Load data 
dat <- read_csv("kc_house_data.csv")

# Examine data structure 
#str(dat)

#summary(dat)

Observations

• House Price Table
  • 1 ID Variable: id
  • 12 Numeric Variables: price, bedrooms, bathrooms, sqft_living, sqft_lot, floors, sqft_basement, sqft_above, lat, long, sqft_living15, sqft_lot15
  • 5 factor variables: waerfront, view, condition, grade, zipcode
  • 3 date variables: date, yr_built, yr_renovated
  • 21613 house records
  • Mean of units 8.579, Median 7
  • Mean price 3.763, Median 3.790
    
  • ID represents house ID
  • Average price is $540,088
  • Average floor size is 2080 sqft
  • Average year built is 1971

2.1. Data Dictionary

• This includes information about the columns of our interest
• date_sold: date on which the house is sold
• month_sold: month in which the house is sold
• year_sold: year in which the house is sold
• selling_price: house sold at what price
• sqft_living: size of the living portion of the house
• price_per_sqft: selling_price/sqft_living
• bedrooms: number of bedrooms
• bathrooms: number of bathrooms
• sqft_lot: size of the lot of the house
• floors: number of fllors
• waterfront: with or without waterfront view: 0 = no view, 1 = view
• condition: condition of the house ranges from 1 to 5, according to King County system.
• grade: grade of the house according to King County grading system
• yr_built: year in which house built
• zipcode: zipcode of the areas
• lat: latitude of the house
• long: longitude of the house
• renovated: if the house is renovated or not: 0 = no, 1 = yes

2.2. Filtering, mutating and selecting required Columns

# Modify columns types
# condition: 1 to 5
# Grade: 1 to 13
house_data <- dat %>%
  drop_na() %>%
  filter(bedrooms <= 11) %>%
  mutate(renovated = ifelse(yr_renovated == 0, 0, 1)) %>%
  mutate(zipcode = as.factor(zipcode),
    year_sold = year(date),
         month_sold = month(date)) %>%
  mutate(year_sold = as.factor(year_sold),
         month_sold = as.factor(month_sold),
         condition = as.factor(condition),
         grade = as.factor(grade),
         bedrooms = as.factor(bedrooms),
         bathrooms = as.factor(bathrooms),
         floors = as.factor(floors),
         waterfront = as.factor(waterfront),
         renovated = as.factor(renovated)) %>%
  mutate(price_per_sqft = price/sqft_living) %>%
  select(date, month_sold, year_sold, price, price_per_sqft, bedrooms, bathrooms, sqft_living, sqft_lot, floors, waterfront, condition, grade, yr_built, zipcode, lat, long, renovated) %>%
  rename(date_sold = date,
         selling_price = price)


summary(house_data)

##    date_sold                     month_sold   year_sold   
##  Min.   :2014-05-02 00:00:00   5      :2414   2014:14632  
##  1st Qu.:2014-07-22 00:00:00   4      :2231   2015: 6980  
##  Median :2014-10-16 00:00:00   7      :2211               
##  Mean   :2014-10-29 04:46:26   6      :2179               
##  3rd Qu.:2015-02-17 00:00:00   8      :1940               
##  Max.   :2015-05-27 00:00:00   10     :1878               
##                                (Other):8759               
##  selling_price     price_per_sqft      bedrooms      bathrooms   
##  Min.   :  75000   Min.   : 87.59   3      :9824   2.5    :5380  
##  1st Qu.: 321838   1st Qu.:182.29   4      :6882   1      :3852  
##  Median : 450000   Median :244.63   2      :2760   1.75   :3047  
##  Mean   : 540084   Mean   :264.15   5      :1601   2.25   :2047  
##  3rd Qu.: 645000   3rd Qu.:318.32   6      : 272   2      :1930  
##  Max.   :7700000   Max.   :810.14   1      : 199   1.5    :1446  
##                                     (Other):  74   (Other):3910  
##   sqft_living       sqft_lot       floors      waterfront condition
##  Min.   :  290   Min.   :    520   1  :10679   0:21449    1:   30  
##  1st Qu.: 1426   1st Qu.:   5040   1.5: 1910   1:  163    2:  172  
##  Median : 1910   Median :   7619   2  : 8241              3:14031  
##  Mean   : 2080   Mean   :  15107   2.5:  161              4: 5679  
##  3rd Qu.: 2550   3rd Qu.:  10688   3  :  613              5: 1700  
##  Max.   :13540   Max.   :1651359   3.5:    8                       
##                                                                    
##      grade         yr_built       zipcode           lat       
##  7      :8980   Min.   :1900   98103  :  601   Min.   :47.16  
##  8      :6068   1st Qu.:1951   98038  :  590   1st Qu.:47.47  
##  9      :2615   Median :1975   98115  :  583   Median :47.57  
##  6      :2038   Mean   :1971   98052  :  574   Mean   :47.56  
##  10     :1134   3rd Qu.:1997   98117  :  553   3rd Qu.:47.68  
##  11     : 399   Max.   :2015   98042  :  548   Max.   :47.78  
##  (Other): 378                  (Other):18163                  
##       long        renovated
##  Min.   :-122.5   0:20698  
##  1st Qu.:-122.3   1:  914  
##  Median :-122.2            
##  Mean   :-122.2            
##  3rd Qu.:-122.1            
##  Max.   :-121.3            
## 

attach(house_data)

4.1 Univariate non-graphical – Categorical

# Function to get the relevant grouped data frames
# df -> dataframe
# groupVariable -> the group by column 
# output -> the resulting data frame
getGroupedUnivariateData <- function(df, groupVariable) {
  # group_by() grouped variable
  # summarise() reduces variable to descriptive stat
  # count is the new variable name, n() is a counting function
  # percent is a new variable, sum() and nrow() are functions
  # tot_selling_price -> total selling price per grouped variable
  # order_by() tot_selling_price
  result <- df %>%             
    group_by_(groupVariable) %>%     
    summarise(count = n(),   
              percent = (sum(count) / nrow(df)) * 100,
              tot_revenue = sum(selling_price),   
              avg_revenue = (mean(selling_price))) %>%
    arrange(desc(tot_revenue)) %>%
    as.data.frame() 
  
  return(result)
}

# price and price per sqft by zipcode
house_data_month <- getGroupedUnivariateData(house_data, "month_sold")

house_data_month %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# price and price per sqft by zipcode
house_data_bedrooms <- getGroupedUnivariateData(house_data, "bedrooms")

house_data_bedrooms %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# price and price per sqft by zipcode
house_data_floors <- getGroupedUnivariateData(house_data, "floors")

house_data_floors %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# price and price per sqft by zipcode
house_data_waterfront <- getGroupedUnivariateData(house_data, "waterfront")

house_data_waterfront %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# price and price per sqft by zipcode
house_data_zipcode <- getGroupedUnivariateData(house_data, "zipcode")

house_data_zipcode %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# price and price per sqft by zipcode
house_data_renovated <- getGroupedUnivariateData(house_data, "renovated")

house_data_renovated %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

4.2. Univariate graphical code – Categorical

# Code histograms using grid.arrange so can see all quant variables together group by Ship.Mode
grid.arrange(
  house_data_bedrooms %>% 
    ggplot(aes(x = reorder(bedrooms, -count), y = count)) +  
    xlab("bedrooms") + 
    theme_classic() +
      geom_bar(stat = "identity", fill = "darkolivegreen3"),
  house_data_floors %>% 
    ggplot(aes(x = reorder(floors, -count), y = count)) +
    xlab("floors") +
    theme_classic() +
      geom_bar(stat = "identity", fill = "darkolivegreen3"),
  house_data_waterfront %>% 
    ggplot(aes(x = reorder(waterfront, -count), y = count)) +
    xlab("waterfront") +
    theme_classic() +
      geom_bar(stat = "identity", fill = "darkolivegreen3"),
  house_data_zipcode %>% 
    ggplot(aes(x = reorder(zipcode, -count), y = count)) +
    xlab("zipcode") +
    theme_classic() +
      geom_bar(stat = "identity", fill = "darkolivegreen3"),
  house_data_renovated %>% 
    ggplot(aes(x = reorder(renovated, -count), y = count)) +
    xlab("renovated") +
    theme_classic() +
      geom_bar(stat = "identity", fill = "darkolivegreen3"),
  ncol = 2
) 

4.3. Univariate non-graphical – Quantitative

# Get descriptive stats 
summary(house_data)

##    date_sold                     month_sold   year_sold   
##  Min.   :2014-05-02 00:00:00   5      :2414   2014:14632  
##  1st Qu.:2014-07-22 00:00:00   4      :2231   2015: 6980  
##  Median :2014-10-16 00:00:00   7      :2211               
##  Mean   :2014-10-29 04:46:26   6      :2179               
##  3rd Qu.:2015-02-17 00:00:00   8      :1940               
##  Max.   :2015-05-27 00:00:00   10     :1878               
##                                (Other):8759               
##  selling_price     price_per_sqft      bedrooms      bathrooms   
##  Min.   :  75000   Min.   : 87.59   3      :9824   2.5    :5380  
##  1st Qu.: 321838   1st Qu.:182.29   4      :6882   1      :3852  
##  Median : 450000   Median :244.63   2      :2760   1.75   :3047  
##  Mean   : 540084   Mean   :264.15   5      :1601   2.25   :2047  
##  3rd Qu.: 645000   3rd Qu.:318.32   6      : 272   2      :1930  
##  Max.   :7700000   Max.   :810.14   1      : 199   1.5    :1446  
##                                     (Other):  74   (Other):3910  
##   sqft_living       sqft_lot       floors      waterfront condition
##  Min.   :  290   Min.   :    520   1  :10679   0:21449    1:   30  
##  1st Qu.: 1426   1st Qu.:   5040   1.5: 1910   1:  163    2:  172  
##  Median : 1910   Median :   7619   2  : 8241              3:14031  
##  Mean   : 2080   Mean   :  15107   2.5:  161              4: 5679  
##  3rd Qu.: 2550   3rd Qu.:  10688   3  :  613              5: 1700  
##  Max.   :13540   Max.   :1651359   3.5:    8                       
##                                                                    
##      grade         yr_built       zipcode           lat       
##  7      :8980   Min.   :1900   98103  :  601   Min.   :47.16  
##  8      :6068   1st Qu.:1951   98038  :  590   1st Qu.:47.47  
##  9      :2615   Median :1975   98115  :  583   Median :47.57  
##  6      :2038   Mean   :1971   98052  :  574   Mean   :47.56  
##  10     :1134   3rd Qu.:1997   98117  :  553   3rd Qu.:47.68  
##  11     : 399   Max.   :2015   98042  :  548   Max.   :47.78  
##  (Other): 378                  (Other):18163                  
##       long        renovated
##  Min.   :-122.5   0:20698  
##  1st Qu.:-122.3   1:  914  
##  Median :-122.2            
##  Mean   :-122.2            
##  3rd Qu.:-122.1            
##  Max.   :-121.3            
## 

4.4. Univariate graphical – Quantitative

# Code histograms using ggplot() so we can see all quant variables

grid.arrange(
house_data %>% 
  ggplot(aes(selling_price/1000)) +
    geom_histogram(fill = "darkolivegreen3") +
  xlab("selling price (in 1000 $)")+
  theme_classic(),

house_data %>% 
  ggplot(aes(price_per_sqft)) +
    geom_histogram(fill = "darkolivegreen3") +
  xlab("price per sqft (in $)") +
  theme_classic(),

house_data %>% 
  ggplot(aes(sqft_living)) +
    geom_histogram(fill = "darkolivegreen3") +
  xlab("sqft of home")+
  theme_classic(),

house_data %>% 
  ggplot(aes(lat)) +
    geom_histogram(fill = "darkolivegreen3") +
  xlab("latitude") +
  theme_classic(), 

house_data %>% 
  ggplot(aes(long)) +
    geom_histogram(fill = "darkolivegreen3") +
  xlab("longitude")+
  theme_classic()
)

# get box plot of all the quanitative variables
par(mfrow = c(2, 2))
boxplot(selling_price/1000, main = "Selling Price / 1000")
boxplot(price_per_sqft, main = "price per sqft")
boxplot(sqft_living, main = "sqft living")

4.5. Multivariate non-graphical – Categorical

# Make cross-tabs between two categorical variables using janitor package
# cross-tab of bedrooms and floors
house_data %>% 
  tabyl(bedrooms, floors) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2) %>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# Make cross-tabs between two categorical variables using janitor package
# cross-tab of bedrooms and floors
house_data %>% 
  tabyl(bedrooms, month_sold) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2)%>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# cross-tab of bedrooms and waterfront

house_data %>% 
  tabyl(bedrooms, waterfront) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2)%>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# cross-tab of bedrooms and condition
house_data %>% 
  tabyl(bedrooms, condition) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2) %>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# cross-tab of floors and waterfront
house_data %>% 
  tabyl(floors, waterfront) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2) %>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# cross-tab of floors and condition
house_data %>% 
  tabyl(floors, condition) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2) %>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# cross-tab of waterfront and condition
house_data %>% 
  tabyl(waterfront, condition) %>% 
  adorn_totals(where = c("row", "col")) %>%
  adorn_percentages(denominator = "all") %>%
  adorn_pct_formatting(digits = 2) %>%
  adorn_title() %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

4.6. Multivariate graphical code – Categorical

#Function for heatmap
 heat_map <- function (x, y,y_title,x_title){
   x<-enquo(x)
   y<-enquo(y)
   
   
   house_data %>%
     group_by(!!x,!!y) %>%
     summarise(count = n())  %>%
     ggplot(aes(!!x,!!y)) +
     geom_tile(aes(fill = -count))+
     ylab(y_title) + xlab(x_title)+
     theme_classic() +
     scale_fill_continuous(guide = guide_legend(title = "Count")) +
     scale_fill_gradient(low = "white", high = "darkolivegreen3")
     
 }

grid.arrange(
heat_map(bedrooms, floors, "floors", "bedrooms"),
heat_map(bedrooms, waterfront, "waterfront", "bedrooms"),
heat_map(bedrooms, condition, "condition", "bedrooms"),
heat_map(bedrooms, renovated, "renovated", "bedrooms"),
heat_map(month_sold, condition, "condition", "month sold"),
nrow=3)

4.7. Multivariate non-graphical – Quantitative

The standard measure between quantitative variables is correlation

# Find correlation of quantitative variables
cor_plot <- house_data %>%
  select(selling_price, price_per_sqft, sqft_living, yr_built, lat, long)

cor(cor_plot) 

##                selling_price price_per_sqft sqft_living    yr_built
## selling_price     1.00000000      0.5547069  0.70204664  0.05402275
## price_per_sqft    0.55470685      1.0000000 -0.09229020 -0.28983503
## sqft_living       0.70204664     -0.0922902  1.00000000  0.31803659
## yr_built          0.05402275     -0.2898350  0.31803659  1.00000000
## lat               0.30699843      0.4720228  0.05255216 -0.14809274
## long              0.02163710     -0.2359974  0.24020927  0.40933763
##                        lat       long
## selling_price   0.30699843  0.0216371
## price_per_sqft  0.47202276 -0.2359974
## sqft_living     0.05255216  0.2402093
## yr_built       -0.14809274  0.4093376
## lat             1.00000000 -0.1354811
## long           -0.13548114  1.0000000

4.8. Multivariate graphical code – Quantitative

# Correlation plot

house_data %>% 
  select(date_sold, selling_price, price_per_sqft, sqft_living, yr_built, lat, long) %>%   pairs.panels(hist.col="darkolivegreen3")

5.1. Multi-variate non graphical: categorical and quantitative

bedrooms_bathrooms_average_price <- house_data %>%
  group_by(bedrooms, bathrooms) %>%
  summarise(Total_Revenue = sum(selling_price)) %>%
  spread(bedrooms, Total_Revenue) %>%
  as.data.frame()

bedrooms_bathrooms_average_price %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

bedrooms_bathrooms_average_sqft <- house_data %>%
  group_by(bedrooms, bathrooms) %>%
  summarise(Average_sqft = mean(sqft_living)) %>%
  spread(bedrooms, Average_sqft) %>%
  as.data.frame()

bedrooms_bathrooms_average_sqft %>%
   kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

5.2. Multi-variate graphical: categorical and quantitative

# how does revenue vary by date sold and what is the impact of waterfront view and renovation on their revenue


house_data %>% 
  mutate(waterfront = ifelse(waterfront == 1, "Waterfront View", "No Waterfront View"),
         renovated = ifelse(renovated ==1, "Renovated", "Not Renovated")) %>%
  group_by(renovated, waterfront) %>% 
  ggplot(aes(x = date_sold, y = selling_price/1000)) +
  geom_line(color = "darkolivegreen3") +
  theme_classic() +
  facet_grid(renovated ~ waterfront) +
labs(y = 'Selling price (, 1000 $)', x = 'Date Sold')

# how do sales vary by sqft_living, sqft_lot and house condition

sqft_living_lot_condition <- ggplot(house_data, aes(x = sqft_living, y = sqft_lot, color = condition)) + 
geom_point(alpha = 0.5) +
   ylim(0, 1000000) +
  geom_smooth(method=lm, se=FALSE, color="black") +
theme_classic()+
theme(legend.title = element_text(size=10),
      plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
labs(x = 'Living Area (sq.ft)', y = 'Lot Area (sq.ft)', title = "Sales are more for houses having good condition",
     subtitle = "House built in 1900 - 2015") +
guides(color = guide_legend(title = 'Condition')) +
scale_x_continuous(breaks=seq(0, 12000, 2000)) +
scale_color_brewer(palette="Paired")

 sqft_living_lot_condition

# how do sales vary by sqft_living, sqft_lot and number of bedrooms

ggplot(house_data, aes(x = sqft_living, y = sqft_lot, color = bedrooms)) + 
geom_point(alpha = 0.5) +
   ylim(0, 1000000) +
  geom_smooth(method=lm, se=FALSE, color="black") +
theme_classic()+
theme(legend.title = element_text(size=10),
      plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
labs(x = 'Living Area (sq.ft)', y = 'Lot Area (sq.ft)', title = "House Sales in King County, USA",
     subtitle = "House built in 1900 - 2015") +
guides(color = guide_legend(title = 'Bedrooms')) +
scale_x_continuous(breaks=seq(0, 12000, 2000)) +
scale_color_brewer(palette="Paired")

6.1. Multiple Linear Regression

# Linear regression
mod <- lm(log(price_per_sqft) ~  date_sold + bedrooms + sqft_lot + floors + condition + grade + zipcode + yr_built + waterfront + renovated + lat + long, data = house_data)

# Review output
summary(mod) 

## 
## Call:
## lm(formula = log(price_per_sqft) ~ date_sold + bedrooms + sqft_lot + 
##     floors + condition + grade + zipcode + yr_built + waterfront + 
##     renovated + lat + long, data = house_data)
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -1.23272 -0.12619  0.00437  0.13033  1.12689 
## 
## Coefficients:
##                Estimate Std. Error t value Pr(>|t|)    
## (Intercept)  -6.682e+01  8.257e+00  -8.093 6.13e-16 ***
## date_sold     2.485e-09  1.511e-10  16.449  < 2e-16 ***
## bedrooms1     2.186e-01  6.486e-02   3.370 0.000752 ***
## bedrooms2     7.486e-02  6.324e-02   1.184 0.236487    
## bedrooms3    -5.773e-02  6.314e-02  -0.914 0.360570    
## bedrooms4    -1.592e-01  6.318e-02  -2.520 0.011737 *  
## bedrooms5    -2.458e-01  6.337e-02  -3.879 0.000105 ***
## bedrooms6    -3.207e-01  6.449e-02  -4.973 6.63e-07 ***
## bedrooms7    -4.464e-01  7.220e-02  -6.183 6.42e-10 ***
## bedrooms8    -4.593e-01  8.683e-02  -5.290 1.24e-07 ***
## bedrooms9    -5.899e-01  1.088e-01  -5.423 5.93e-08 ***
## bedrooms10   -6.216e-01  1.398e-01  -4.448 8.73e-06 ***
## bedrooms11   -2.998e-01  2.251e-01  -1.332 0.182911    
## sqft_lot      5.009e-07  3.900e-08  12.844  < 2e-16 ***
## floors1.5    -1.002e-02  5.820e-03  -1.722 0.085114 .  
## floors2      -3.518e-02  4.301e-03  -8.178 3.04e-16 ***
## floors2.5    -4.123e-02  1.768e-02  -2.332 0.019710 *  
## floors3      -6.283e-02  1.076e-02  -5.836 5.41e-09 ***
## floors3.5    -6.795e-02  7.740e-02  -0.878 0.379962    
## condition2    1.389e-01  4.353e-02   3.192 0.001416 ** 
## condition3    2.430e-01  4.054e-02   5.995 2.06e-09 ***
## condition4    2.745e-01  4.056e-02   6.769 1.33e-11 ***
## condition5    3.187e-01  4.079e-02   7.813 5.83e-15 ***
## grade3       -7.571e-01  2.620e-01  -2.890 0.003854 ** 
## grade4       -7.225e-01  2.318e-01  -3.118 0.001826 ** 
## grade5       -9.144e-01  2.298e-01  -3.979 6.95e-05 ***
## grade6       -9.321e-01  2.296e-01  -4.059 4.95e-05 ***
## grade7       -9.634e-01  2.296e-01  -4.195 2.74e-05 ***
## grade8       -9.472e-01  2.297e-01  -4.125 3.73e-05 ***
## grade9       -8.822e-01  2.297e-01  -3.841 0.000123 ***
## grade10      -8.357e-01  2.298e-01  -3.637 0.000276 ***
## grade11      -7.598e-01  2.300e-01  -3.304 0.000955 ***
## grade12      -6.779e-01  2.307e-01  -2.939 0.003299 ** 
## grade13      -4.753e-01  2.375e-01  -2.001 0.045391 *  
## zipcode98002 -5.045e-03  1.942e-02  -0.260 0.795007    
## zipcode98003  1.037e-02  1.736e-02   0.597 0.550303    
## zipcode98004  1.005e+00  3.157e-02  31.841  < 2e-16 ***
## zipcode98005  6.287e-01  3.374e-02  18.636  < 2e-16 ***
## zipcode98006  5.926e-01  2.755e-02  21.509  < 2e-16 ***
## zipcode98007  5.939e-01  3.485e-02  17.043  < 2e-16 ***
## zipcode98008  6.014e-01  3.309e-02  18.176  < 2e-16 ***
## zipcode98010  3.229e-01  2.959e-02  10.911  < 2e-16 ***
## zipcode98011  2.584e-01  4.301e-02   6.009 1.90e-09 ***
## zipcode98014  2.857e-01  4.725e-02   6.046 1.51e-09 ***
## zipcode98019  2.423e-01  4.662e-02   5.198 2.03e-07 ***
## zipcode98022  2.269e-01  2.573e-02   8.819  < 2e-16 ***
## zipcode98023 -6.497e-02  1.597e-02  -4.068 4.75e-05 ***
## zipcode98024  4.535e-01  4.159e-02  10.905  < 2e-16 ***
## zipcode98027  4.885e-01  2.830e-02  17.263  < 2e-16 ***
## zipcode98028  2.214e-01  4.177e-02   5.302 1.16e-07 ***
## zipcode98029  6.154e-01  3.234e-02  19.032  < 2e-16 ***
## zipcode98030  5.268e-02  1.908e-02   2.761 0.005774 ** 
## zipcode98031  6.780e-02  1.987e-02   3.412 0.000646 ***
## zipcode98032 -3.875e-02  2.307e-02  -1.679 0.093071 .  
## zipcode98033  6.560e-01  3.584e-02  18.303  < 2e-16 ***
## zipcode98034  3.991e-01  3.843e-02  10.386  < 2e-16 ***
## zipcode98038  2.249e-01  2.144e-02  10.487  < 2e-16 ***
## zipcode98039  1.145e+00  4.270e-02  26.820  < 2e-16 ***
## zipcode98040  7.977e-01  2.790e-02  28.595  < 2e-16 ***
## zipcode98042  1.096e-01  1.827e-02   5.996 2.05e-09 ***
## zipcode98045  4.811e-01  3.963e-02  12.138  < 2e-16 ***
## zipcode98052  5.335e-01  3.659e-02  14.579  < 2e-16 ***
## zipcode98053  4.815e-01  3.919e-02  12.284  < 2e-16 ***
## zipcode98055  9.511e-02  2.213e-02   4.297 1.74e-05 ***
## zipcode98056  2.596e-01  2.406e-02  10.790  < 2e-16 ***
## zipcode98058  1.505e-01  2.093e-02   7.192 6.58e-13 ***
## zipcode98059  3.060e-01  2.359e-02  12.967  < 2e-16 ***
## zipcode98065  4.347e-01  3.648e-02  11.915  < 2e-16 ***
## zipcode98070  1.894e-01  2.765e-02   6.849 7.65e-12 ***
## zipcode98072  3.338e-01  4.279e-02   7.801 6.43e-15 ***
## zipcode98074  5.096e-01  3.464e-02  14.711  < 2e-16 ***
## zipcode98075  5.360e-01  3.330e-02  16.097  < 2e-16 ***
## zipcode98077  3.396e-01  4.451e-02   7.628 2.48e-14 ***
## zipcode98092  7.278e-02  1.736e-02   4.194 2.76e-05 ***
## zipcode98102  8.172e-01  3.690e-02  22.149  < 2e-16 ***
## zipcode98103  6.522e-01  3.466e-02  18.819  < 2e-16 ***
## zipcode98105  7.850e-01  3.556e-02  22.077  < 2e-16 ***
## zipcode98106  2.257e-01  2.563e-02   8.804  < 2e-16 ***
## zipcode98107  6.873e-01  3.570e-02  19.255  < 2e-16 ***
## zipcode98108  2.196e-01  2.829e-02   7.764 8.61e-15 ***
## zipcode98109  8.223e-01  3.674e-02  22.382  < 2e-16 ***
## zipcode98112  8.665e-01  3.261e-02  26.573  < 2e-16 ***
## zipcode98115  6.271e-01  3.519e-02  17.821  < 2e-16 ***
## zipcode98116  6.031e-01  2.857e-02  21.108  < 2e-16 ***
## zipcode98117  6.000e-01  3.563e-02  16.841  < 2e-16 ***
## zipcode98118  3.483e-01  2.500e-02  13.931  < 2e-16 ***
## zipcode98119  8.162e-01  3.467e-02  23.539  < 2e-16 ***
## zipcode98122  6.968e-01  3.093e-02  22.527  < 2e-16 ***
## zipcode98125  3.807e-01  3.803e-02  10.009  < 2e-16 ***
## zipcode98126  4.189e-01  2.626e-02  15.953  < 2e-16 ***
## zipcode98133  2.576e-01  3.927e-02   6.561 5.47e-11 ***
## zipcode98136  5.599e-01  2.691e-02  20.804  < 2e-16 ***
## zipcode98144  5.373e-01  2.876e-02  18.685  < 2e-16 ***
## zipcode98146  1.635e-01  2.407e-02   6.792 1.14e-11 ***
## zipcode98148  1.042e-01  3.277e-02   3.179 0.001480 ** 
## zipcode98155  2.355e-01  4.083e-02   5.768 8.12e-09 ***
## zipcode98166  2.054e-01  2.204e-02   9.322  < 2e-16 ***
## zipcode98168 -2.832e-02  2.331e-02  -1.215 0.224434    
## zipcode98177  3.713e-01  4.098e-02   9.061  < 2e-16 ***
## zipcode98178  6.972e-02  2.404e-02   2.900 0.003732 ** 
## zipcode98188  3.145e-02  2.469e-02   1.274 0.202718    
## zipcode98198  2.797e-02  1.869e-02   1.497 0.134436    
## zipcode98199  6.427e-01  3.380e-02  19.017  < 2e-16 ***
## yr_built     -3.692e-04  9.026e-05  -4.090 4.32e-05 ***
## waterfront1   5.898e-01  1.764e-02  33.442  < 2e-16 ***
## renovated1    3.972e-02  7.866e-03   5.049 4.47e-07 ***
## lat           4.240e-01  8.513e-02   4.981 6.38e-07 ***
## long         -4.071e-01  6.119e-02  -6.652 2.96e-11 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.2156 on 21504 degrees of freedom
## Multiple R-squared:  0.7033, Adjusted R-squared:  0.7018 
## F-statistic: 476.4 on 107 and 21504 DF,  p-value: < 2.2e-16

# plot residuals to check for patterns
par(mfrow = c(2, 2))
plot(mod$fitted.values, mod$residuals)
plot(yr_built, mod$residuals)
plot(sqft_lot, mod$residuals)
plot(date_sold, mod$residuals)

# Are price per sqft same across all regions?
# Not tidyverse so have to use base code
chisq.test(table(price_per_sqft, zipcode))

## 
##  Pearson's Chi-squared test
## 
## data:  table(price_per_sqft, zipcode)
## X-squared = 1157700, df = 1146800, p-value = 3.513e-13

# Pull out the coefficients and confidence interval for table and graph
coeff <- summary(mod)$coefficients # get coefficients and related stats
coeff_CI <- as.data.frame(cbind(coeff[-1, ], confint(mod)[-1, ])) # find and bind CI, remove Intercept 

# Rename results data frame
names(coeff_CI) <- c("estimate", "se", "t", "pval","low_CI","high_CI") 

kable(round(coeff_CI,3)) %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

kable(round(coeff_CI[order(coeff_CI$pval, decreasing = FALSE), ], 3)) %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

house_good <- house_data %>% select(sqft_living, sqft_lot, price_per_sqft, lat, long)

p1 <- ggplot(house_good, aes(x = sqft_living, y = price_per_sqft)) +
geom_point(color = 'black', size = 0.5) +
geom_smooth(method="lm", color = 'darkolivegreen3', size = 0.5, se = FALSE) +
theme_classic() + 
labs(x = 'Sqft living', y = 'Price per sqft (USD)') + 
scale_y_continuous(labels = scales::comma)

p2 <- ggplot(house_good, aes(x = sqft_lot, y = price_per_sqft)) +
geom_point(color = 'black', size = 0.5) +
geom_smooth(method="lm", color = 'darkolivegreen3', size = 0.5, se = FALSE) +
theme_classic() + 
labs(x = 'Sqft lot', y = 'Price per sqft (USD)') + 
scale_y_continuous(labels = scales::comma)

p3 <- ggplot(house_good, aes(x = lat, y = price_per_sqft)) +
geom_point(color = 'black', size = 0.5) +
geom_smooth(method="lm", color = 'darkolivegreen3', size = 0.5) +
theme_classic() + 
labs(x = 'Latitude', y = 'Price per sqft (USD)') + 
scale_y_continuous(labels = scales::comma)

p4 <- ggplot(house_good, aes(x = long, y = price_per_sqft)) +
geom_point(color = 'black', size = 0.5) +
geom_smooth(method="lm", color = 'darkolivegreen3', size = 0.5) +
theme_classic() + 
labs(x = 'Longitude', y = 'Price per sqft (USD)') + 
scale_y_continuous(labels = scales::comma)

grid.arrange(p1,p2,p3,p4, nrow = 2,
              top = "House Sales in King County, USA")

# Reorder for more clarity
(g1 <- ggplot(coeff_CI, aes(x = estimate, y = reorder(row.names(coeff_CI),desc(pval)))) +
  geom_point(size = 3) +
  xlim(min(coeff_CI$low_CI), max(coeff_CI$high_CI)) +
  ylab("Variable") +
  xlab("Coefficient") +
  theme_bw() +
  geom_segment(aes(yend = reorder(row.names(coeff_CI),desc(pval))), 
               xend = coeff_CI$high_CI, color = "red") +
  geom_segment(aes(yend = reorder(row.names(coeff_CI),desc(coeff_CI$pval))), 
               xend = coeff_CI$low_CI, color = "red") +
   xlab("Coefficient with Confidence Interval") +
  geom_vline(xintercept = 0, color = "darkolivegreen3") + # line gives context
   theme_classic()
)

6.2. K Means Clustering

# Reference: littlemissdata.com
# Clustering Price for Map Index Purpose

set.seed(1)

priceclustering <- house_data %>% select(selling_price)

# Build a kmeans model
model_km3 <- kmeans(priceclustering, centers = 5)

# Extract the cluster assignment vector from the kmeans model
clust_km3 <- model_km3$cluster

# Create a new dataframe appending the cluster assignment
price_cluster <- mutate(house_data, cluster = clust_km3)

price_index <- function(x){
                   for(i in 1:nrow(x)){
                       if(x[i,"cluster"] == 1){
                       x[i,"index"] = '75,000 - 409,500'
                       } else if(x[i,"cluster"] == 5) {
                       x[i,"index"] = '409,501 - 673,500'
                       } else if(x[i,"cluster"] == 3){
                       x[i,"index"] = '673,501 - 1,137,500'
                       } else if(x[i,"cluster"] == 2){
                       x[i,"index"] = '1,137,501 - 2,140,500'
                       } else {
                       x[i,"index"] = '2,140,501 - 7,700,000'
                       }    
                   }
              return(x)
              }

price_map <- price_index(price_cluster)
price_map$index <- as.factor(price_map$index)
    
house1 <- filter(price_map, cluster == 1)
house2 <- filter(price_map, cluster == 2)
house3 <- filter(price_map, cluster == 3)
house4 <- filter(price_map, cluster == 4)
house5 <- filter(price_map, cluster == 5)

# Color Index    
pal <- colorFactor(palette = c("blue", "red", "green", "pink", "orange"), 
                   levels = c('75,000 - 409,500', '409,501 - 673,500', '673,501 - 1,137,500', 
                              '1,137,501 - 2,140,500', '2,140,501 - 7,700,000'))

# Draw a Map
map_price_area <- leaflet(options = leafletOptions(minZoom = 9, dragging = TRUE)) %>% 
addProviderTiles(provider = 'CartoDB')%>%
addCircleMarkers(data = house1, radius = 1, 
                 popup = ~paste0("<b>", 'USD ', selling_price, "</b>", "<br/>", "House area (sqft): ", 
                                 sqft_living, "<br/>", "Lot area (sqft): ", sqft_lot),
                 color = ~pal(index),  group = '75,000 - 409,500') %>%
addCircleMarkers(data = house5, radius = 1, 
                 popup = ~paste0("<b>", 'USD ', selling_price, "</b>", "<br/>", "House area (sqft): ", 
                                 sqft_living, "<br/>", "Lot area (sqft): ", sqft_lot),
                 color = ~pal(index),  group = '409,501 - 673,500') %>%
addCircleMarkers(data = house3, radius = 1, 
                 popup = ~paste0("<b>", 'USD ', selling_price, "</b>", "<br/>", "House area (sqft): ", 
                                 sqft_living, "<br/>", "Lot area (sqft): ", sqft_lot),
                 color = ~pal(index),  group = '673,501 - 1,137,500') %>%
addCircleMarkers(data = house2, radius = 1, 
                 popup = ~paste0("<b>", 'USD ', selling_price, "</b>", "<br/>", "House area (sqft): ", 
                                 sqft_living, "<br/>", "Lot area (sqft): ", sqft_lot),
                 color = ~pal(index),  group = '1,137,501 - 2,140,500') %>%
addCircleMarkers(data = house4, radius = 1, 
                 popup = ~paste0("<b>", 'USD ', selling_price, "</b>", "<br/>", "House area (sqft): ", 
                                 sqft_living, "<br/>", "Lot area (sqft): ", sqft_lot),
                 color = ~pal(index),  group = '2,140,501 - 7,700,000') %>%
setView(lng = -122.001008, lat = 47.474443, zoom = 9) %>%
addLegend(pal = pal, 
          values = c('75,000 - 409,500', '409,501 - 673,500', '673,501 - 1,137,500', 
                     '1,137,501 - 2,140,500', '2,140,501 - 7,700,000'),
          opacity = 0.5, title = "Price Range", position = "bottomright") %>%
addLayersControl(overlayGroups = c('75,000 - 409,500', '409,501 - 673,500', '673,501 - 1,137,500', '
                                  1,137,501 - 2,140,500', '2,140,501 - 7,700,000'), position = "bottomleft")

map_price_area

7.1 Question 1: How does the house sales and revenue vary by zipcode and year built?

# How does house sales and revenue vary by zipcode

house_data %>%
  group_by(zipcode) %>% 
  summarise(Count = n(),
  Average_Price_per_sqft = mean(price_per_sqft),
  Total_Revenue = sum(selling_price),
  Average_Revenue = mean(selling_price)) %>%
  arrange(desc(Average_Price_per_sqft, Count)) %>%
  kable() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
  row_spec(1, background = "#e5f5e0")

# How does average price per sqft vary by zipcode
zipcode_avg_price_per_sqft <- house_data %>%
  group_by(zipcode) %>% 
  summarise(Count = n(),
  Average_Price_per_sqft = mean(price_per_sqft),
  Total_Revenue = sum(selling_price)) %>%
  arrange(desc(Average_Price_per_sqft, Count, bedrooms)) %>%
  slice(1:10) %>%
  ggplot(aes(x = reorder(zipcode, -Average_Price_per_sqft), y = Average_Price_per_sqft)) +
  geom_bar(stat="identity", fill = "darkolivegreen3") +
  theme_classic()  +
  theme(text = element_text(size = 10))+
  theme(axis.ticks.y = element_blank(), 
        axis.text.y = element_blank(),
        legend.position = "none",
        panel.background = element_blank()) +
  theme(plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
 labs(x = "Zipcode",
      y = "Average Price per sqft ($)",
      title = "Top 10 most expensive areas in King County",
      subtitle = "") +
  geom_text(aes(label = str_c('$',round(Average_Price_per_sqft, 0))), size = 3, vjust = 1.5, position = position_dodge(0.9))

zipcode_avg_price_per_sqft

# How does number of houses sold vary by zipcode

house_data %>%
  group_by(zipcode) %>% 
  summarise(Count = n(),
  Average_Price_per_sqft = mean(price_per_sqft),
  Total_Revenue = sum(selling_price)) %>%
  arrange(desc(Average_Price_per_sqft, Count, bedrooms)) %>%
  slice(1:10) %>%
  ggplot(aes(x = reorder(zipcode, -Count), y = Count)) +
  geom_bar(stat="identity", fill = "darkolivegreen3") +
  theme_classic()  +
  theme(text = element_text(size = 10))+
  theme(axis.ticks.y = element_blank(), 
        axis.text.y = element_blank(),
        legend.position = "none",
        panel.background = element_blank()) +
  theme(plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
 labs(x = "Zipcode",
      y = "Number of sales",
      title = "Top 10 zipcodes with most number of houses sold",
      subtitle = "") +
  geom_text(aes(label = str_c(round(Count, 0))), size = 3, vjust = 1.5, position = position_dodge(0.9))

# how does selling price of the house vary by zipcode

house_data %>%
  group_by(zipcode) %>% 
  summarise(Count = n(),
  Average_Price_per_sqft = mean(price_per_sqft),
  Total_Revenue = sum(selling_price)) %>%
  arrange(desc(Average_Price_per_sqft, Count, bedrooms)) %>%
  slice(1:10) %>%
  ggplot(aes(x = reorder(zipcode, -Total_Revenue), y = Total_Revenue/1000000)) +
  geom_bar(stat="identity", fill = "darkolivegreen3") +
  theme_classic()  +
  theme(text = element_text(size = 10))+
  theme(axis.ticks.y = element_blank(), 
        axis.text.y = element_blank(),
        legend.position = "none",
        panel.background = element_blank()) +
  theme(plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
 labs(x = "Zipcode",
      y = "Total revenue (in mil $)",
      title = "Top 10 zipcodes with maximum revenue",
      subtitle = "") +
  geom_text(aes(label = str_c(round(Total_Revenue/1000000, 0), 'mil $')), size = 3, vjust = 1.5, position = position_dodge(0.9))

# how does sales vary by year built

house_data %>%
group_by(yr_built) %>%
summarise(n = n()) %>%
ggplot(aes(x = yr_built, y = n)) +
geom_line(color = 'darkolivegreen3') +
geom_smooth(method="lm", color = 'black', size = 0.5, se = FALSE) +
theme_classic() +
theme(plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
labs(x = 'Year Built', y = 'Total', title = "Includes number of houses built in 1900 - 2015 in King County",
     subtitle = "Sales are more for the houses which are recently built") +
scale_x_continuous(breaks=seq(1900, 2015, 10))

# how does sales vary by date sold
house_sales_by_date <- house_data %>%
group_by(date_sold) %>%
summarise(n = n()) %>%
ggplot(aes(x = date_sold, y = n)) +
geom_line(color = 'darkolivegreen3') +
geom_smooth(method="lm", color = 'black', size = 0.5, se = FALSE) +
theme_classic() +
theme(plot.title = element_text(hjust = 0, face = 'bold', color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
labs(x = 'Date Sold', y = 'Number of houses', title = "Overall sales have dropped between May 2014 - May 2015",
     subtitle = "")
  


house_sales_by_date 

# how does sales vary by date sold
house_data %>%
group_by(date_sold) %>%
summarise(n = n(),
          total_revenue = sum(selling_price)) %>%
ggplot(aes(x = date_sold, y = total_revenue/1000000)) +
geom_line(color = 'darkolivegreen3') +
geom_smooth(method="lm", color = 'black', size = 0.5, se = FALSE) +
theme_classic() +
theme(plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
labs(x = 'Date Sold', y = 'Total Revenue (in mil $)', title = "Includes daily revenue of sales between May 2014 - May 2015",
     subtitle = "There is slight decrease in revenue between May 2014 and May 2015")

7.2. How does the price vary by condition and grade of the house according to King County grading system?

# how does sales vary by grade and condition
house_data %>%
  group_by(grade, condition) %>%
  summarize (Revenue_sum = sum(selling_price)) %>%
  ggplot(aes(x = reorder(grade, -Revenue_sum), y = Revenue_sum/1000000, fill = condition)) +
  geom_bar(stat="identity") +
  theme_classic()  +
  theme(text = element_text(size = 10))+
  theme(plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
 labs(x = "Grades",
      y = "Total Revenue (in mil USD)",
      title = "Houses with Grade 7, on King County scale, have maximum revenue",
      subtitle = "Houses with condition 3 have maximum revenue among all grades.",
      caption = "There are 13 grades and 5 conditions")  +
  scale_fill_manual(values=c("darkolivegreen4", "yellow4","darkolivegreen3", "darkolivegreen2", "darkolivegreen1") )

# how does revenue vary by condition and living area
ggplot(house_data, aes(x = selling_price/1000, y = sqft_living, color = condition)) + 
geom_point(alpha = 0.5) +
  geom_smooth(method=lm, se=FALSE, color="black") +
theme_classic()+
theme(legend.title = element_text(size=10),
      plot.title = element_text(hjust = 0, face = 'bold',color = 'black'),
      plot.subtitle = element_text(face = "italic")) +
labs(x = 'Sold At (,000 $)', y = 'Living Area (sq.ft)', title = "Price increases as living area increases.",
     subtitle = "More sales are for lower area and condition 3 houses.") +
guides(color = guide_legend(title = 'Condition')) +
scale_color_brewer(palette="Paired")